package homework.jdbc.dao;

import homework.jdbc.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
//操作user表
public class UserDao {
    public List<User> findAll() throws SQLException {
        Connection conn =JDriverUtils.getConnection();//连接
        ResultSet set=null;
        Statement statement =null;
                List<User> list=new ArrayList<>(); //创建集合 存储每一个对象
        try {
            statement = conn.createStatement();
            String sql="select*from student";
            set=statement.executeQuery(sql);  // 查询 结果放入ResultSet集合-->对象set
            while (set.next()){
                User user=new User();
                user.setId(set.getInt(1));
                user.setUsername(set.getString(2));
                user.setPassword(set.getString(3));
                user.setEmail(set.getString(4));
                user.setPhone(set.getString(5));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            set.close();
            statement.close();
        }
        return null;
    }

    public boolean insert(User user) throws SQLException {
        Connection conn=JDriverUtils.getConnection();
        String sql="insert into student (id,Username,password,email,phone) values(?,?,?,?,?)";
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getEmail());
            ps.setString(5,user.getPhone());
            int num=ps.executeUpdate();
            if (num==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            conn.close();
            ps.close();
        }

        return false;
    }

    public boolean update(int id){
        return false;
    }

    public boolean delete(int id) throws SQLException {
        Connection conn=JDriverUtils.getConnection();
        ResultSet result=null;
        PreparedStatement ps=null;
        try {
        String sql="delete*from student where id = ?";
        ps=conn.prepareStatement(sql);
        ps.setInt(1,id);

        while (result.next()){
            User user=new User();
            user.setId(result.getInt(1));
            user.setUsername(result.getString(2));
            user.setPassword(result.getString(3));
            user.setEmail(result.getString(4));
            user.setPhone(result.getString(5));
            return true;
        }} catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public User findById(int id) throws SQLException {

        Connection conn =JDriverUtils.getConnection();//连接
        ResultSet result=null;
        PreparedStatement ps =null;  //预处理 ，带参处理

        try {

            String sql="select*from student where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);//'1', ?的位置
            result=ps.executeQuery();
            while (result.next()){
                User user=new User();
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                user.setEmail(result.getString(4));
                user.setPhone(result.getString(5));
                return user;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            result.close();
            ps.close();
        }

        return null;
    }
}
